CS157a Spring 2002Practice Final

The practice final is below. Here are some facts about the actual final: (a) The final will be in class May 23 from 12:15am-2:30pm (Sec 4). (b) It is closed book, closed notes. Nothing will be permitted on your desk except your pen (pencil) and test. (c) You should bring photo ID. (d) There will be more than one version of the test. Each version will be of comparable difficulty. (e) If your cell-phone or beeper goes off you will be excused from the test at that point and graded on what you have done till your excusal. (f) One problem (less typos) on the actual test will be from the practice test.

1. A Ship is an entity with the following attributes: SName and Owner
   where SName is the key. Ship_Movement is a weak entity type
   which is involved in a weak entity relationship History with Ship.
   It has Lattitude, Longitude, and TimeStamp as its attributes, the latter
   being the partial key. Draw the ER diagram representing this information
   and then show how to convert it into the Relational Model using the
   algorithm from class.

2. For each of the following Relational Calculus queries, determine which
   are safe and which aren't:

   (a) {t.A, s.B | R(t) }
   (b) {t.A, s.B | R(t) AND S(s)}
   (c) {t.A | (forall s)( D(s) OR t.A = s.C)}

3. Express the following as relational calculus queries:
   
   (a) Find the first name and last name of the Employee with the 
       largest salary.
   (b) Find the birth dates of all Employees with at least two Dependents.
   (c) R(A,B)*S(B,C)*T(C,D).

4. Explain the following Oracle terms:
   (a) System Global Area
   (b) Data Block
   (c) Trigger

5. Explain how one can specify foreign key constraints when designing an
   Access database. Give an example of how to graphically do a natural
   join on two tables using Access.

6. Write a short JDBC program which connects to your account on sigma at school
   (if this were on the Final, I would give the URL but not driver) and 
   prints out the last names and salaries of all employees with salaries 
   over 50000.

7. Give examples of the following situations:
   (a) A table in first normal form but not second normal form
   (b) A table in 2NF but not 3NF.
   (c) A table in BCNF but not dependency preserving

8. Show step by step using the algorithm from class how to calculate the
   minimal cover of the following functional dependencies:
   {X->YZ, W->X, Y->ZV}

9. Define multivalued dependency. Give an example of a table in 4NF
   as well as one not in 4NF. Can a table be in 4NF and not BCNF?
   Explain why or why not?

10. Use Armstrong axioms to prove the following identities:
    1. {AB->C, BC->A, C->B} |= C->A
    2. {X->W, A->B, B->C} |= XA -> WC
    3. B subset D, {B->C, A->D} |= A -> C